/*
  Copyright 2009 Smart Enterprise Solution Corp.
  Email: contact@ses.vn - Website: http://www.ses.vn
  KimNgan Project.
*/
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using DOAN.DO;
/// <summary>
/// Summary description for ThanhvienDAL
/// </summary>
namespace DOAN.DAL
{

    public class ThanhvienDAL : BaseDAL
    {
        #region Private Variables

        #endregion

        #region Public Constructors
        public ThanhvienDAL()
        {
            //
            // TODO: Add constructor logic here
            //

        }
        #endregion



        #region Public Methods
        public int Insert(ThanhvienDO objThanhvienDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_Insert";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Tentaikhoan", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Tentaikhoan;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Maquyen", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Maquyen;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Matkhau", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Matkhau;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Email", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Email;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Sodienthoai", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Sodienthoai;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Hovaten", SqlDbType.NVarChar);
            Sqlparam.Value = objThanhvienDO.Hovaten;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Ngaysinh", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Ngaysinh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Gioitinh", SqlDbType.Int);
            Sqlparam.Value = objThanhvienDO.Gioitinh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Lop", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Lop;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@ID", SqlDbType.Int);
            Sqlparam.Direction = ParameterDirection.ReturnValue;
            Sqlcomm.Parameters.Add(Sqlparam);


            int result = base.ExecuteNoneQuery(Sqlcomm);

            if (!Convert.IsDBNull(Sqlcomm.Parameters["@ID"]))
                result = Convert.ToInt32(Sqlcomm.Parameters["@ID"].Value);

            return result;
        }

        public int Update(ThanhvienDO objThanhvienDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_UpdateByPK";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Tentaikhoan", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Tentaikhoan;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Maquyen", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Maquyen;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Matkhau", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Matkhau;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Email", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Email;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Sodienthoai", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Sodienthoai;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Hovaten", SqlDbType.NVarChar);
            Sqlparam.Value = objThanhvienDO.Hovaten;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Ngaysinh", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Ngaysinh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Gioitinh", SqlDbType.Int);
            Sqlparam.Value = objThanhvienDO.Gioitinh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Lop", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Lop;
            Sqlcomm.Parameters.Add(Sqlparam);



            Sqlparam = new SqlParameter("@ErrorCode", SqlDbType.Int);
            Sqlparam.Direction = ParameterDirection.ReturnValue;
            Sqlcomm.Parameters.Add(Sqlparam);

            int result = base.ExecuteNoneQuery(Sqlcomm);

            if (!Convert.IsDBNull(Sqlcomm.Parameters["@ErrorCode"]))
                result = Convert.ToInt32(Sqlcomm.Parameters["@ErrorCode"].Value);

            return result;


        }

        public int Delete(ThanhvienDO objThanhvienDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_DeleteByPK";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@Tentaikhoan", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Tentaikhoan;
            Sqlcomm.Parameters.Add(Sqlparam);



            int result = base.ExecuteNoneQuery(Sqlcomm);
            return result;
        }

        public int DeleteAll()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_DeleteAll";

            int result = base.ExecuteNoneQuery(Sqlcomm);
            return result;
        }

        public ThanhvienDO login(string tk, string mk)
        {
            ThanhvienDO objThanhvienDO = new ThanhvienDO();
            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "Dangnhaptv";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Tendangnhap", SqlDbType.VarChar);
            Sqlparam.Value = tk;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Matkhau2", SqlDbType.VarChar);
            Sqlparam.Value = mk;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataRow dr = null;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
                if (!Convert.IsDBNull(dr["Tentaikhoan"]))
                    objThanhvienDO.Tentaikhoan = Convert.ToString(dr["Tentaikhoan"]);
                if (!Convert.IsDBNull(dr["Maquyen"]))
                    objThanhvienDO.Maquyen = Convert.ToString(dr["Maquyen"]);
                if (!Convert.IsDBNull(dr["Matkhau"]))
                    objThanhvienDO.Matkhau = Convert.ToString(dr["Matkhau"]);
                if (!Convert.IsDBNull(dr["Email"]))
                    objThanhvienDO.Email = Convert.ToString(dr["Email"]);
                if (!Convert.IsDBNull(dr["Sodienthoai"]))
                    objThanhvienDO.Sodienthoai = Convert.ToString(dr["Sodienthoai"]);
                if (!Convert.IsDBNull(dr["Hovaten"]))
                    objThanhvienDO.Hovaten = Convert.ToString(dr["Hovaten"]);
                if (!Convert.IsDBNull(dr["Ngaysinh"]))
                    objThanhvienDO.Ngaysinh = Convert.ToString(dr["Ngaysinh"]);
                if (!Convert.IsDBNull(dr["Gioitinh"]))
                    objThanhvienDO.Gioitinh = Convert.ToInt32(dr["Gioitinh"]);
                if (!Convert.IsDBNull(dr["Lop"]))
                    objThanhvienDO.Lop = Convert.ToString(dr["Lop"]);

            }
            return objThanhvienDO;
        }

        public DataTable Select2(ThanhvienDO objThanhvienDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetByPK";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Tentaikhoan", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Tentaikhoan;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }

        public ThanhvienDO Select(ThanhvienDO objThanhvienDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetByPK";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Tentaikhoan", SqlDbType.VarChar);
            Sqlparam.Value = objThanhvienDO.Tentaikhoan;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataRow dr = null;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
                if (!Convert.IsDBNull(dr["Tentaikhoan"]))
                    objThanhvienDO.Tentaikhoan = Convert.ToString(dr["Tentaikhoan"]);
                if (!Convert.IsDBNull(dr["Maquyen"]))
                    objThanhvienDO.Maquyen = Convert.ToString(dr["Maquyen"]);
                if (!Convert.IsDBNull(dr["Matkhau"]))
                    objThanhvienDO.Matkhau = Convert.ToString(dr["Matkhau"]);
                if (!Convert.IsDBNull(dr["Email"]))
                    objThanhvienDO.Email = Convert.ToString(dr["Email"]);
                if (!Convert.IsDBNull(dr["Sodienthoai"]))
                    objThanhvienDO.Sodienthoai = Convert.ToString(dr["Sodienthoai"]);
                if (!Convert.IsDBNull(dr["Hovaten"]))
                    objThanhvienDO.Hovaten = Convert.ToString(dr["Hovaten"]);
                if (!Convert.IsDBNull(dr["Ngaysinh"]))
                    objThanhvienDO.Ngaysinh = Convert.ToString(dr["Ngaysinh"]);
                if (!Convert.IsDBNull(dr["Gioitinh"]))
                    objThanhvienDO.Gioitinh = Convert.ToInt32(dr["Gioitinh"]);
                if (!Convert.IsDBNull(dr["Lop"]))
                    objThanhvienDO.Lop = Convert.ToString(dr["Lop"]);

            }
            return objThanhvienDO;
        }

        public ThanhvienDO SelectobjByEmail(string email)
        {
            ThanhvienDO objThanhvienDO = new ThanhvienDO();
            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetByEmail";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@email ", SqlDbType.VarChar);
            Sqlparam.Value = email;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataRow dr = null;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
                if (!Convert.IsDBNull(dr["Tentaikhoan"]))
                    objThanhvienDO.Tentaikhoan = Convert.ToString(dr["Tentaikhoan"]);
                if (!Convert.IsDBNull(dr["Maquyen"]))
                    objThanhvienDO.Maquyen = Convert.ToString(dr["Maquyen"]);
                if (!Convert.IsDBNull(dr["Matkhau"]))
                    objThanhvienDO.Matkhau = Convert.ToString(dr["Matkhau"]);
                if (!Convert.IsDBNull(dr["Email"]))
                    objThanhvienDO.Email = Convert.ToString(dr["Email"]);
                if (!Convert.IsDBNull(dr["Sodienthoai"]))
                    objThanhvienDO.Sodienthoai = Convert.ToString(dr["Sodienthoai"]);
                if (!Convert.IsDBNull(dr["Hovaten"]))
                    objThanhvienDO.Hovaten = Convert.ToString(dr["Hovaten"]);
                if (!Convert.IsDBNull(dr["Ngaysinh"]))
                    objThanhvienDO.Ngaysinh = Convert.ToString(dr["Ngaysinh"]);
                if (!Convert.IsDBNull(dr["Gioitinh"]))
                    objThanhvienDO.Gioitinh = Convert.ToInt32(dr["Gioitinh"]);
                if (!Convert.IsDBNull(dr["Lop"]))
                    objThanhvienDO.Lop = Convert.ToString(dr["Lop"]);

            }
            return objThanhvienDO;
        }

        public DataTable SelectByEmail(string email)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetByEmail";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@email ", SqlDbType.VarChar);
            Sqlparam.Value = email;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }


        public ArrayList SelectAll1()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetAll";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;
            ArrayList arrThanhvienDO = new ArrayList();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    ThanhvienDO objThanhvienDO = new ThanhvienDO();
                    if (!Convert.IsDBNull(dr["Tentaikhoan"]))
                        objThanhvienDO.Tentaikhoan = Convert.ToString(dr["Tentaikhoan"]);
                    if (!Convert.IsDBNull(dr["Maquyen"]))
                        objThanhvienDO.Maquyen = Convert.ToString(dr["Maquyen"]);
                    if (!Convert.IsDBNull(dr["Matkhau"]))
                        objThanhvienDO.Matkhau = Convert.ToString(dr["Matkhau"]);
                    if (!Convert.IsDBNull(dr["Email"]))
                        objThanhvienDO.Email = Convert.ToString(dr["Email"]);
                    if (!Convert.IsDBNull(dr["Sodienthoai"]))
                        objThanhvienDO.Sodienthoai = Convert.ToString(dr["Sodienthoai"]);
                    if (!Convert.IsDBNull(dr["Hovaten"]))
                        objThanhvienDO.Hovaten = Convert.ToString(dr["Hovaten"]);
                    if (!Convert.IsDBNull(dr["Ngaysinh"]))
                        objThanhvienDO.Ngaysinh = Convert.ToString(dr["Ngaysinh"]);
                    if (!Convert.IsDBNull(dr["Gioitinh"]))
                        objThanhvienDO.Gioitinh = Convert.ToInt32(dr["Gioitinh"]);
                    if (!Convert.IsDBNull(dr["Lop"]))
                        objThanhvienDO.Lop = Convert.ToString(dr["Lop"]);
                    arrThanhvienDO.Add(objThanhvienDO);
                }
            }
            return arrThanhvienDO;
        }

        public DataTable SelectAll()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetAll";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }

        public DataTable GetGV()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetGV";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }
        public DataTable GetTV()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetAllTV";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }

        public DataTable SelectByLop(string lop)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_GetLop";

            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@lop", SqlDbType.VarChar);
            Sqlparam.Value = lop;
            Sqlcomm.Parameters.Add(Sqlparam);


            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }

        public DataTable GetAllLop()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_getAllLop";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }

        public DataTable TimKiem(int tt,string tk)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spThanhvien_TimKiem";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@tt", SqlDbType.VarChar);
            Sqlparam.Value = tt;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Tenmon", SqlDbType.VarChar);
            Sqlparam.Value = tk;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }

        #endregion

    }

}
